<?php
declare (strict_types = 1);

namespace app\webadmin\controller;

use app\webadmin\BaseController;
use app\webadmin\model\JingmiduWz as JingmiduWzList;
use app\webadmin\model\PianyiduBz;
use app\webadmin\validate\PianyiduCheck;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Font;
use think\facade\Db;
use think\facade\View;
use \think\facade\Log;
use think\exception\ValidateException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Calculation\Statistical;

class Pianyidu extends BaseController
{
    public function index()
    {
        if (request()->isAjax()) {
            $param = get_params();
            $where = array();
            if (!empty($param['keywords'])) {
                $where[] = ['id|data1', 'like', '%' . $param['keywords'] . '%'];
            }
            $where[] = ['uid', '=', get_login_admin('id')];
            $rows = empty($param['limit']) ? get_config('app.page_size') : $param['limit'];
            $content = PianyiduBz::where($where)
                ->order('id asc')
                ->paginate($rows, false, ['query' => $param])->each(function ($item, $k) {
                    //$item['createtime']=date('Y-m-d H:i:s',$item['create_time']);
                    $item['data1'] = sprint_data(floatval($item['data1']), 2);
                    $item['data2'] = sprint_data(floatval($item['data2']), 2);
                    $item['data3'] = sprint_data(floatval($item['data3']), 2);

                    return $item;
                });
            return table_assign(0, '', $content);
        } else {
            return view();
        }
    }

    //添加/修改
    public function add(){
        $param = get_params();
        if (request()->isAjax()) {
            if (!empty($param['id']) && $param['id'] > 0) {
                //修改
                try {
                    validate(PianyiduCheck::class)->scene('edit')->check($param);
                } catch (ValidateException $e) {
                    // 验证失败 输出错误信息
                    return to_assign(1, $e->getError());
                }
                $param['update_time'] = time();
                $res = PianyiduBz::strict(false)->field(true)->update($param);
                if ($res) {
                    add_log('edit', $param['id'], $param);
                    return to_assign(0, "修改成功");
                }
                return to_assign();
            } else {
                //新增
                try {
                    validate(PianyiduCheck::class)->scene('add')->check($param);
                } catch (ValidateException $e) {
                    // 验证失败 输出错误信息
                    return to_assign(1, $e->getError());
                }
                $param['uid']=get_login_admin('id');
                $param['create_time'] = time();
                $insertId = PianyiduBz::strict(false)->field(true)->insertGetId($param);
                if ($insertId) {
                    add_log('add', $insertId, $param);
                    return to_assign(0, "添加成功");
                }
                return to_assign();
            }
        }
        else{
            $id = isset($param['id']) ? $param['id'] : 0;
            if ($id > 0) {
                $wdx = PianyiduBz::where(['id' => $id])->find();
                if($wdx){
                }
                View::assign('data', $wdx);
            }
            View::assign('id', $id);
            return view();
        }
    }

    //删除数据
    public function delete(){
        //Db::name('sjjy')->where('id','>',0)->delete();
        $id = get_params("id");
        if(empty($id)){
            return to_assign(1, "id不能为空");
        }
        // 软删除
        $db=PianyiduBz::where('id',$id)->find();
        if($db){
            // 软删除
            $db->delete();
            add_log('delete', $id);
            return to_assign(0, "删除成功");
        } else {
            return to_assign(1, "删除失败");
        }
    }

    //清空数据
    public function delete_all(){
        // 软删除
        $param = get_params();
        $db=PianyiduBz::where('uid',get_login_admin('id'))->delete();
        if($db){

            return to_assign(0, "删除成功");
        } else {
            return to_assign(1, "删除失败");
        }
    }

    public function start_jisuan(){
        $param = get_params();
        //最大值 =MAX(B37:D37)
        $data1=floatval($param['data1']);
        $data2=floatval($param['data2']);
        $data3=floatval($param['data3']);
        $bzz=floatval($param['bzwz']);
        $kzbqdd=floatval($param['kzbqdd']);
        //数组1
        $py_array=[$data1,$data2,$data3];
        $max=Statistical::MAX($py_array);
        $min=Statistical::MIN($py_array);
        $jcxs=1.693;
        //平均值的标准不确定度 =((K24-K25)/K26)*(1/K23^0.5)
        $bqdd01=pow(1/3,0.5);
        $bzbqdd=number_format(($max-$min)/$jcxs*$bqdd01,3);
        //有证标准物质标准值的标准不确定度 =C27/2
        $bz_buedingd=number_format($kzbqdd/2,3);
        //偏倚的标准不确定度 =(K27^2+K28^2)^0.5
        $py_bddd=pow(pow($bzbqdd,2)+pow($bz_buedingd,2),0.5);
        $py_bddd=number_format($py_bddd,3);
        //检测平均值的偏倚 =ABS(AVERAGE(B24:D24)-B27)
        $avg_py=abs(Statistical::AVERAGE($py_array)-$bzz);
        $avg_py=number_format($avg_py,3);
        //结论比较 |bias|<2×ubias
        if($avg_py<2*$py_bddd){
            $results='偏倚不显著';
        }else{
            $results='<font color=red>偏倚显著</font>';
        }
        $html = '<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">重复次数</td><td>n</td><td>3</td></tr>";
        $html .= "<tr><td align=\"center\">最大值</td><td>Cmax</td><td>{$max}</td></tr>";
        $html .= "<tr><td align=\"center\">最小值</td><td>Cmin</td><td>{$min}</td></tr>";
        $html .= "<tr><td align=\"center\">极差系数（C因子）</td><td>C</td><td>{$jcxs}</td></tr>";
        $html .= "<tr><td align=\"center\">平均值的标准不确定度</td><td>u(c ̅) </td><td>{$bzbqdd}</td></tr>";
        $html .= "<tr><td align=\"center\">有证标准物质标准值的标准不确定度</td><td>uCRM</td><td>{$bz_buedingd}</td></tr>";
        $html .= "<tr><td align=\"center\">偏倚的标准不确定度</td><td>ubias</td><td>{$py_bddd}</td></tr>";
        $html .= "<tr><td align=\"center\">检测平均值的偏倚</td><td>|bias|</td><td>{$avg_py}</td></tr>";
        $html .= "<tr><td align=\"center\">阳性定量质控品偏倚显著性判定</td><td>|bias|<2×ubias</td><td></td></tr>";
        $html .= "<tr><td align=\"center\">结论</td><td>{$results}</td><td></td></tr>";
        $html.='</table>';
        $html.= "<br><br>";
        return $html;
    }

    //导出数据
    public function export_data(){
        $param = get_params();
        //最大值 =MAX(B37:D37)
        $data1=floatval($param['data1']);
        $data2=floatval($param['data2']);
        $data3=floatval($param['data3']);
        $bzz=floatval($param['bzwz']);
        $kzbqdd=floatval($param['kzbqdd']);
        //数组1
        $py_array=[$data1,$data2,$data3];
        $max=Statistical::MAX($py_array);
        $min=Statistical::MIN($py_array);
        $jcxs=1.693;
        //平均值的标准不确定度 =((K24-K25)/K26)*(1/K23^0.5)
        $bqdd01=pow(1/3,0.5);
        $bzbqdd=number_format(($max-$min)/$jcxs*$bqdd01,3);
        //有证标准物质标准值的标准不确定度 =C27/2
        $bz_buedingd=number_format($kzbqdd/2,3);
        //偏倚的标准不确定度 =(K27^2+K28^2)^0.5
        $py_bddd=pow(pow($bzbqdd,2)+pow($bz_buedingd,2),0.5);
        $py_bddd=number_format($py_bddd,3);
        //检测平均值的偏倚 =ABS(AVERAGE(B24:D24)-B27)
        $avg_py=abs(Statistical::AVERAGE($py_array)-$bzz);
        $avg_py=number_format($avg_py,3);
        //结论比较 |bias|<2×ubias
        if($avg_py<2*$py_bddd){
            $results='偏倚不显著';
        }else{
            $results='<font color=red>偏倚显著</font>';
        }
        //导出表格
        $title='用常规样品评定测量方法精密度不确定度';
        $objExcel = new Spreadsheet();
        $objWriter = IOFactory::createWriter($objExcel, 'Xlsx');
        $objActSheet = $objExcel->getActiveSheet(0);
        $objActSheet->setTitle($title);//设置excel的标题

        //$objActSheet->getStyle('A1:C1')->getFont()->setSize(14);
        //$objActSheet->getStyle('A1:C1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        // 设置列宽
        $objActSheet->getColumnDimension('A')->setWidth(50);
        $objActSheet->getColumnDimension('B')->setWidth(20);
        $objActSheet->getColumnDimension('C')->setWidth(20);

        $objActSheet->getRowDimension(1)->setRowHeight(25);      // 设置行高

        //设置水平、垂直居中
        $styleArray = [
            //设置字体样式
            'font' => [
                'name' => 'Arial',
                'color' => [
                    'rgb' => Color::COLOR_BLACK
                ]
            ],
            //设置边框线样式
            'borders' => [
            ],
            //对齐样式
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            //是否使用前缀
            'quotePrefix'    => true
        ];
        //表格1
        $objActSheet->getStyle('A1:C10')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A1:C10')->applyFromArray($styleArray);

        $objActSheet->setCellValue('A1', '重复次数');
        $objActSheet->setCellValue('B1', 'n');
        $objActSheet->setCellValue('C1', '3');
        $objActSheet->getRowDimension(1)->setRowHeight(25);

        $objActSheet->setCellValue('A2', '最大值');
        $objActSheet->setCellValue('B2', 'Cmax');
        $objActSheet->setCellValue('C2', $max);
        $objActSheet->getRowDimension(2)->setRowHeight(25);

        $objActSheet->setCellValue('A3', '最小值');
        $objActSheet->setCellValue('B3', 'Cmin');
        $objActSheet->setCellValue('C3', $min);
        $objActSheet->getRowDimension(3)->setRowHeight(25);

        $objActSheet->setCellValue('A4', '极差系数（C因子）');
        $objActSheet->setCellValue('B4', 'C');
        $objActSheet->setCellValue('C4', $jcxs);
        $objActSheet->getRowDimension(4)->setRowHeight(25);

        $objActSheet->setCellValue('A5', '平均值的标准不确定度');
        $objActSheet->setCellValue('B5', 'u(c ̅)');
        $objActSheet->setCellValue('C5', $bzbqdd);
        $objActSheet->getRowDimension(5)->setRowHeight(25);

        $objActSheet->setCellValue('A6', '有证标准物质标准值的标准不确定度');
        $objActSheet->setCellValue('B6', 'uCRM');
        //$objActSheet->setCellValue('C6', $bz_buedingd);
        $objActSheet->getRowDimension(6)->setRowHeight(25);
        $objActSheet->getCell('C6')->setValueExplicit($bz_buedingd, DataType::TYPE_STRING);

        $objActSheet->setCellValue('A7', '偏倚的标准不确定度');
        $objActSheet->setCellValue('B7', 'ubias');
        //$objActSheet->setCellValue('C7', $py_bddd);
        $objActSheet->getCell('C7')->setValueExplicit($py_bddd, DataType::TYPE_STRING);

        $objActSheet->getRowDimension(7)->setRowHeight(25);

        $objActSheet->setCellValue('A8', '检测平均值的偏倚');
        $objActSheet->setCellValue('B8', '|bias|');
        //$objActSheet->setCellValue('C8', $avg_py);
        $objActSheet->getCell('C8')->setValueExplicit($avg_py, DataType::TYPE_STRING);
        $objActSheet->getRowDimension(8)->setRowHeight(25);

        $objActSheet->setCellValue('A9', '阳性定量质控品偏倚显著性判定');
        $objActSheet->setCellValue('B9', '|bias|<2×ubias	');
        $objActSheet->setCellValue('C9', '');
        $objActSheet->getRowDimension(9)->setRowHeight(25);

        $objActSheet->setCellValue('A10', '结论');
        $objActSheet->setCellValue('B10', $results);
        $objActSheet->setCellValue('C10', '');
        $objActSheet->getRowDimension(10)->setRowHeight(25);

        //保存路径
        $expFileType='xlsx';
        $time = date('YmdHis',time());
        $expFileName = $title."_".$time.".".$expFileType;
        //设置保存路径
        $basePath = request()->env('ROOT_PATH').'excel';
        $baseUrl  = DIRECTORY_SEPARATOR .implode(DIRECTORY_SEPARATOR, [date('Y-m-d',time())]). DIRECTORY_SEPARATOR;
        $path = $basePath.$baseUrl;
        //路径不存在则创建路径
        if(!is_dir($path)){
            mkdir($path, 0777, true);
        }
        $path = $path.$expFileName;
        // 保存文件
        $objWriter->save($path);
        $downpath = str_replace('\\','/',$path);
        $downpath2=config('app.DOMAIN_URL').'/'.$downpath;
        $response = ['downloadurl'=>$downpath2];
        return json($response);
        /*
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="用有证标准物质评定偏倚不确定度（'. date('Y-m-d')  .'）'.'.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');*/
    }

    //实验室
    public function sys_yangping(){
        return view();
    }

    //开始计算
    public function start_jisuan_sys(){
        $param = get_params();
        //Log::info('param='.json_encode($param));
        //最大值 =MAX(B37:D37)
        $data1=floatval($param['data1']);
        $data2=floatval($param['data2']);
        $data3=floatval($param['data3']);
        $sdata1=floatval($param['sdata1']);
        $sdata2=floatval($param['sdata2']);
        $sdata3=floatval($param['sdata3']);
        //数组1
        $data_array = [$data1,$data2,$data3];
        //数组2
        $sdata_array = [$sdata1,$sdata2,$sdata3];
        $cmax=Statistical::MAX($data_array);
        $cmin=Statistical::MIN($data_array);
        $c=1.693;
        //标准不确定度 =((K37-K38)/K39)*(1/K36^0.5)
        $bzbqdd = (($cmax-$cmin)/$c)*pow(1/3,0.5);
        $bzbqdd =number_format($bzbqdd,3);
        //重复检测数据平均值的标准不确定度 =(MAX(B41:D41)-MIN(B41:D41))/K26*(1/(3^0.5))
        $scmax=Statistical::MAX($sdata_array);
        $scmin=Statistical::MIN($sdata_array);
        $K26=1.693;
        $cf_bzbqdd=($scmax-$scmin)/$K26*(1/pow(3,0.5));
        $cf_bzbqdd2=number_format($cf_bzbqdd,3);
        //偏倚的标准不确定度 =(K40^2+K41^2)^0.5
        $py_bzbqdd=pow($bzbqdd,2)+pow($cf_bzbqdd,2);
        $py_bzbqdd2=pow($py_bzbqdd,0.5);
        $py_bzbqdd21=number_format($py_bzbqdd2,3);
        //检测平均值的相对偏倚 ==ABS(AVERAGE(B41:D41)-AVERAGE(B37:D37))/AVERAGE(B37:D37)*100
        $avg1=Statistical::AVERAGE($sdata_array);
        $avg2=Statistical::AVERAGE($data_array);
        $jcavg_py=abs($avg1-$avg2)/$avg2*100;
        $jcavg_py2=number_format($jcavg_py,3);
        if($jcavg_py2<25){
            $result='偏倚不显著';
        }else{
            $result='<font color=red>偏倚显著</font>';
        }


        $html = '<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">重复次数</td><td>n</td><td>3</td></tr>";
        $html .= "<tr><td align=\"center\">最大值</td><td>Cmax</td><td>{$cmax}</td></tr>";
        $html .= "<tr><td align=\"center\">最小值</td><td>Cmin</td><td>{$cmin}</td></tr>";
        $html .= "<tr><td align=\"center\">极差系数（C因子）</td><td>C</td><td>{$c}</td></tr>";
        $html .= "<tr><td align=\"center\">粉末质控品标称值的标准不确定度</td><td>u<sub>E</sub></td><td>{$bzbqdd}</td></tr>";
        $html .= "<tr><td align=\"center\">重复检测数据平均值的标准不确定度</td><td>u<sub>(c ̅)</sub> </td><td>{$cf_bzbqdd2}</td></tr>";
        $html .= "<tr><td align=\"center\">偏倚的标准不确定度</td><td>u<sub>bias</sub></td><td>{$py_bzbqdd21}</td></tr>";
        $html .= "<tr><td align=\"center\">检测平均值的相对偏倚</td><td>u<sub>bias</sub></td><td>{$jcavg_py2}</td></tr>";
        $html .= "<tr><td align=\"center\">阳性定量质控品偏倚显著性判定</td><td>u<sub>bias</sub></td><td>|bias|rel<25%</td></tr>";
        $html .= "<tr><td align=\"center\">结论</td><td>u<sub>bias</sub></td><td>{$result}</td></tr>";
        $html.="</table>";
        return $html;
    }

    //数据导出
    public function export_data_sys(){
        $param = get_params();
        Log::info('param='.json_encode($param));
        //最大值 =MAX(B37:D37)
        $data1=floatval($param['data1']);
        $data2=floatval($param['data2']);
        $data3=floatval($param['data3']);
        $sdata1=floatval($param['sdata1']);
        $sdata2=floatval($param['sdata2']);
        $sdata3=floatval($param['sdata3']);
        //数组1
        $data_array = [$data1,$data2,$data3];
        //数组2
        $sdata_array = [$sdata1,$sdata2,$sdata3];
        $cmax=Statistical::MAX($data_array);
        $cmin=Statistical::MIN($data_array);
        $c=1.693;
        //标准不确定度 =((K37-K38)/K39)*(1/K36^0.5)
        $bzbqdd = (($cmax-$cmin)/$c)*pow(1/3,0.5);
        $bzbqdd =number_format($bzbqdd,3);
        //重复检测数据平均值的标准不确定度 =(MAX(B41:D41)-MIN(B41:D41))/K26*(1/(3^0.5))
        $scmax=Statistical::MAX($sdata_array);
        $scmin=Statistical::MIN($sdata_array);
        $K26=1.693;
        $cf_bzbqdd=($scmax-$scmin)/$K26*(1/pow(3,0.5));
        $cf_bzbqdd2=number_format($cf_bzbqdd,3);
        //偏倚的标准不确定度 =(K40^2+K41^2)^0.5
        $py_bzbqdd=pow($bzbqdd,2)+pow($cf_bzbqdd,2);
        $py_bzbqdd2=pow($py_bzbqdd,0.5);
        $py_bzbqdd21=number_format($py_bzbqdd2,3);
        //检测平均值的相对偏倚 ==ABS(AVERAGE(B41:D41)-AVERAGE(B37:D37))/AVERAGE(B37:D37)*100
        $avg1=Statistical::AVERAGE($sdata_array);
        $avg2=Statistical::AVERAGE($data_array);
        $jcavg_py=abs($avg1-$avg2)/$avg2*100;
        $jcavg_py2=number_format($jcavg_py,3);
        if($jcavg_py2<25){
            $result='偏倚不显著';
        }else{
            $result='偏倚显著';
        }
        $title='用实验室配制样品评定偏倚不确定度';
        //导出表格
        $objExcel = new Spreadsheet();
        $objWriter = IOFactory::createWriter($objExcel, 'Xlsx');
        $objActSheet = $objExcel->getActiveSheet(0);
        $objActSheet->setTitle($title);//设置excel的标题

        // 设置列宽
        $objActSheet->getColumnDimension('A')->setWidth(50);
        $objActSheet->getColumnDimension('B')->setWidth(20);
        $objActSheet->getColumnDimension('C')->setWidth(20);

        $objActSheet->getRowDimension(1)->setRowHeight(25);      // 设置行高

        //设置水平、垂直居中
        $styleArray = [
            //设置字体样式
            'font' => [
                'name' => 'Arial',
                'color' => [
                    'rgb' => Color::COLOR_BLACK
                ]
            ],
            //设置边框线样式
            'borders' => [
            ],
            //对齐样式
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            //是否使用前缀
            'quotePrefix'    => true
        ];
        //表格1
        $objActSheet->getStyle('A1:C10')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A1:C10')->applyFromArray($styleArray);

        $objActSheet->setCellValue('A1', '重复次数');
        $objActSheet->setCellValue('B1', 'n');
        $objActSheet->setCellValue('C1', '3');
        $objActSheet->getRowDimension(1)->setRowHeight(25);

        $objActSheet->setCellValue('A2', '最大值');
        $objActSheet->setCellValue('B2', 'Cmax');
        $objActSheet->setCellValue('C2', $cmax);
        $objActSheet->getRowDimension(2)->setRowHeight(25);

        $objActSheet->setCellValue('A3', '最小值');
        $objActSheet->setCellValue('B3', 'Cmin');
        $objActSheet->setCellValue('C3', $cmin);
        $objActSheet->getRowDimension(3)->setRowHeight(25);

        $objActSheet->setCellValue('A4', '极差系数（C因子）');
        $objActSheet->setCellValue('B4', 'C');
        $objActSheet->setCellValue('C4', $c);
        $objActSheet->getRowDimension(4)->setRowHeight(25);

        $objActSheet->setCellValue('A5', '粉末质控品标称值的标准不确定度');
        $objActSheet->setCellValue('B5', 'uE');
        $objActSheet->setCellValue('C5', $bzbqdd);
        $objActSheet->getRowDimension(5)->setRowHeight(25);

        $objActSheet->setCellValue('A6', '重复检测数据平均值的标准不确定度');
        $objActSheet->setCellValue('B6', 'u(c ̅) ');
        $objActSheet->setCellValue('C6', $cf_bzbqdd2);
        $objActSheet->getRowDimension(6)->setRowHeight(25);
        //$objActSheet->getCell('C6')->setValueExplicit($bz_buedingd, DataType::TYPE_STRING);

        $objActSheet->setCellValue('A7', '偏倚的标准不确定度');
        $objActSheet->setCellValue('B7', 'ubias');
        $objActSheet->setCellValue('C7', $py_bzbqdd21);
        //$objActSheet->getCell('C7')->setValueExplicit($py_bddd, DataType::TYPE_STRING);

        $objActSheet->getRowDimension(7)->setRowHeight(25);

        $objActSheet->setCellValue('A8', '检测平均值的相对偏倚');
        $objActSheet->setCellValue('B8', '|bias|rel');
        $objActSheet->setCellValue('C8', $jcavg_py2);
        //$objActSheet->getCell('C8')->setValueExplicit($avg_py, DataType::TYPE_STRING);
        $objActSheet->getRowDimension(8)->setRowHeight(25);

        $objActSheet->setCellValue('A9', '阳性定量质控品偏倚显著性判定');
        $objActSheet->setCellValue('B9', '|bias|rel<25%	');
        $objActSheet->setCellValue('C9', '');
        $objActSheet->getRowDimension(9)->setRowHeight(25);

        $objActSheet->setCellValue('A10', '结论');
        $objActSheet->setCellValue('B10', '偏倚不显著');
        $objActSheet->setCellValue('C10', $result);
        $objActSheet->getRowDimension(10)->setRowHeight(25);

        //保存路径
        $expFileType='xlsx';
        $time = date('YmdHis',time());
        $expFileName = $title."_".$time.".".$expFileType;
        //设置保存路径
        $basePath = request()->env('ROOT_PATH').'excel';
        $baseUrl  = DIRECTORY_SEPARATOR .implode(DIRECTORY_SEPARATOR, [date('Y-m-d',time())]). DIRECTORY_SEPARATOR;
        $path = $basePath.$baseUrl;
        //路径不存在则创建路径
        if(!is_dir($path)){
            mkdir($path, 0777, true);
        }
        $path = $path.$expFileName;
        // 保存文件
        $objWriter->save($path);
        $downpath = str_replace('\\','/',$path);
        $downpath2=config('app.DOMAIN_URL').'/'.$downpath;
        $response = ['downloadurl'=>$downpath2];
        return json($response);
        //从浏览器保存
        /*header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="用实验室配制样品评定偏倚不确定度（'. date('Y-m-d')  .'）'.'.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');*/
    }

    //jiyin_index
    public function jiyin_index(){
        return view();
    }

    //start_jisuan_jiyin
    public function start_jisuan_jiyin(){
        $param = get_params();
        //Log::info('param='.json_encode($param));
        //最大值 =MAX(B37:D37)
        $data1=floatval($param['data1']);
        $data2=floatval($param['data2']);
        $data3=floatval($param['data3']);
        $sdata1=floatval($param['sdata1']);
        $sdata2=floatval($param['sdata2']);
        $sdata3=floatval($param['sdata3']);

        $cdata1=floatval($param['cdata1']);
        $cdata2=floatval($param['cdata2']);
        $cdata3=floatval($param['cdata3']);
        $cdata4=floatval($param['cdata4']);
        $cdata5=floatval($param['cdata5']);

        //阳性定量质控品检测数据平均值的标准不确定度
        $tdata1=floatval($param['tdata1']);
        $tdata2=floatval($param['tdata2']);
        $tdata3=floatval($param['tdata3']);
        $bcz=floatval($param['bcz']);

        //数组1
        $data_array = [$data1,$data2,$data3];
        //数组2
        $sdata_array = [$sdata1,$sdata2,$sdata3];

        //数组3
        $tdata_array = [$tdata1,$tdata2,$tdata3];

        //=AVERAGE(B51:D51)
        $avg1=Statistical::AVERAGE($data_array);
        $avg2=Statistical::AVERAGE($sdata_array);
        //=(MAX(B51:D51)-MIN(B51:D51))/(1.693*3^0.5)
        $avg_bzc=(Statistical::MAX($data_array)-Statistical::MIN($data_array));
        $avg_bzc1=$avg_bzc/(1.693*pow(3,0.5));
        $avg_bzc11=number_format($avg_bzc1);
        //=F51/E51
        $avg_xdbzc=$avg_bzc1/$avg1;
        $avg_xdbzc1=number_format($avg_xdbzc,4);
        //=(MAX(B52:D52)-MIN(B52:D52))/(1.693*3^0.5)
        $avg_bzc2=(Statistical::MAX($sdata_array)-Statistical::MIN($sdata_array));
        $avg_bzc21=$avg_bzc2/(1.693*pow(3,0.5));
        $avg_bzc22=number_format($avg_bzc21);
        //=F52/E52
        $avg_xdbzc2=$avg_bzc21/$avg2;
        $avg_xdbzc21=number_format($avg_xdbzc2,4);

        //基因组DNA质控品标称值的相对不确定度 =(K48^2+K49^2+(D56/(3^0.5))^2+(D57/(3^0.5))^2)^0.5
        $xdbqdd=pow(pow($avg_xdbzc1,2)+pow($avg_xdbzc21,2)+pow($cdata3/(pow(3,0.5)),2)+pow($cdata4/(pow(3,0.5)),2),0.5);
        $xdbqdd1=number_format($xdbqdd,3);

        //基因组DNA质控品标称值的标准不确定度 =D58*K50
        $dna_bzbqdd=$cdata5*$xdbqdd;
        $dna_bzbqdd2=number_format($dna_bzbqdd,3);

        //重复检测数据平均值的标准不确定度 =(MAX(B61:D61)-MIN(B61:D61))/K26*(1/(3^0.5))
        $K26=1.693;
        $cf_bzbqdd=(Statistical::MAX($tdata_array)-Statistical::MIN($tdata_array))/$K26*(1/(pow(3,0.5)));
        $cf_bzbqdd1=number_format($cf_bzbqdd,3);
        //偏倚的标准不确定度 =(K51^2+K52^2)^0.5
        $py_bzbqdd=pow(pow($dna_bzbqdd,2)+pow($cf_bzbqdd,2),0.5);
        $py_bzbqdd2=number_format($py_bzbqdd,3);
        //检测平均值的相对偏倚 =ABS(AVERAGE(B61:D61)-D58)/D58*100
        $jcavg_xdpy=abs(Statistical::AVERAGE($tdata_array)-$cdata5)/$cdata5*100;
        $jcavg_xdpy2=number_format($jcavg_xdpy,3);
        if($jcavg_xdpy2<25){
            $result='偏倚不显著';
        }else{
            $result='偏倚显著';
        }

        $html = '<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">平均值</td><td>平均值标准差</td><td>平均值相对标准差</td></tr>";
        $html .= "<tr><td align=\"center\">{$avg1}</td><td>{$avg_bzc11}</td><td>{$avg_xdbzc1}</td></tr>";
        $html .= "<tr><td align=\"center\">{$avg2}</td><td>{$avg_bzc22}</td><td>{$avg_xdbzc21}</td></tr>";
        $html.="</table>";

        $html.= "<br><br>";
        $html .= '<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">转基因DNA拷贝数浓度相对不确定度</td><td>u</td><td>{$avg_xdbzc1}</td></tr>";
        $html .= "<tr><td align=\"center\">非转基因DNA拷贝数浓度相对不确定度</td><td>u</td><td>{$avg_xdbzc21}</td></tr>";
        $html .= "<tr><td align=\"center\">基因组DNA质控品标称值的相对不确定度</td><td>公式</td><td>{$xdbqdd1}</td></tr>";
        $html .= "<tr><td align=\"center\">基因组DNA质控品标称值的标准不确定度</td><td>公式</td><td>{$dna_bzbqdd2}</td></tr>";
        $html .= "<tr><td align=\"center\">重复检测数据平均值的标准不确定度</td><td>公式</td><td>{$cf_bzbqdd1}</td></tr>";
        $html .= "<tr><td align=\"center\">偏倚的标准不确定度</td><td>u<sub></sub> </td><td>{$py_bzbqdd2}</td></tr>";
        $html .= "<tr><td align=\"center\">检测平均值的相对偏倚</td><td>u<sub>bias</sub></td><td>{$jcavg_xdpy2}</td></tr>";
        $html .= "<tr><td align=\"center\">阳性定量质控品偏倚显著性判定</td><td colspan='2'>|bias|rel<25%</td></tr>";
        $html .= "<tr><td align=\"center\">结论</td><td colspan='2'>{$result}</td></tr>";

       $html.="</table>";
        return $html;
    }
}